package uniandes.dermatologia.resources;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Locale;

import uniandes.dermatologia.models.Anamnesis;
import uniandes.dermatologia.models.Consultation;
import uniandes.dermatologia.models.PhysicalExamination;
import uniandes.dermatologia.resources.dao.AnamnesisDao;
import uniandes.dermatologia.resources.dao.AnamnesisTableDefintion;
import uniandes.dermatologia.resources.dao.AnswerDao;
import uniandes.dermatologia.resources.dao.AnswerTableDefiniton;
import uniandes.dermatologia.resources.dao.AttemptDao;
import uniandes.dermatologia.resources.dao.AttemptTableDefinition;
import uniandes.dermatologia.resources.dao.CaseDao;
import uniandes.dermatologia.resources.dao.CaseTableDefintion;
import uniandes.dermatologia.resources.dao.ExaminationDao;
import uniandes.dermatologia.resources.dao.ExaminationTableDefintion;
import uniandes.dermatologia.resources.dao.OptionDao;
import uniandes.dermatologia.resources.dao.OptionTableDefintion;
import uniandes.dermatologia.resources.dao.QuestionDao;
import uniandes.dermatologia.resources.dao.QuestionTableDefintion;
import uniandes.dermatologia.resources.dao.TestDao;
import uniandes.dermatologia.resources.dao.TestTableDefintion;
import uniandes.dermatologia.resources.models.AnamnesisModel;
import uniandes.dermatologia.resources.models.AnswerModel;
import uniandes.dermatologia.resources.models.AttemptModel;
import uniandes.dermatologia.resources.models.CaseModel;
import uniandes.dermatologia.resources.models.ExaminationModel;
import uniandes.dermatologia.resources.models.OptionModel;
import uniandes.dermatologia.resources.models.QuestionModel;
import uniandes.dermatologia.resources.models.TestModel;
import uniandes.dermatologia.singleton.DermatologiaUniandesApp;
import uniandes.dermatologia.training.CaseLogic;
import uniandes.dermatologia.training.TestLogic;
import uniandes.dermatologia.training.pojos.Answer;
import uniandes.dermatologia.training.pojos.Attempt;
import uniandes.dermatologia.training.pojos.Option;
import uniandes.dermatologia.training.pojos.Question;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBManager {

	private static final String TAG = "DBManager";

	public static final String DB_NAME = "dermatologiauniandes.db";
	public static final int DB_VERSION = 1;

	// Primary Key
	public static final String COL_ID = "_id";

	// Consultation table
	public static final String CONSULTATION_TABLE_NAME = "consultation";
	public static final String COL_CREATED_AT = "created_at";
	public static final String COL_SELECTED_RESULTS = "selected_results";

	// Disease table
	public static final String DISEASE_TABLE_NAME = "disease";
	public static final String COL_RESOURCE_DBPEDIA_URL = "resourceDBpediaURL";

	// Results table constants
	public static final String RESULT_TABLE_NAME = "result";
	public static final String COL_SCORE = "score";
	public static final String COL_UTILITY = "utility";
	
	//User table constants
	public static final String USER_TABLE_NAME = "users";
	public static final String COL_USER_NAME = "user_name";
	public static final String COL_PASSWORD = "password";
	public static final String PASSWORD = "password";
	public static final String EMAIL = "email";
	public static final String COL_TOKEN = "token";
	

	// Result table constants foreign keys
	public static final String COL_ID_CONSULTATION = "id_consult";
	public static final String COL_ID_DISEASE = "id_disease";


	private Context context;
	private DBHelper dbHelper;
	private SQLiteDatabase db;
	
	private AnamnesisDao anamnesisDao;
	private ExaminationDao examinationDao;
	private CaseDao caseDao;
	private TestDao testDao;
	private QuestionDao questionDao;
	private OptionDao optionDao;
	private AttemptDao attemptDao;
	private AnswerDao answerDao;

	public DBManager(Context nContext) {
		this.context = nContext;
		dbHelper = new DBHelper();
	}
	
	public int insertConsultation(Consultation consultation) {

		db = dbHelper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(COL_CREATED_AT, consultation.getConsultationDate().getTime());
		db.insertWithOnConflict(CONSULTATION_TABLE_NAME, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);
		return getMaxId(CONSULTATION_TABLE_NAME);
	}
	
	public void insertDisease(RowDiseaseAbstraction disease) {

		db = dbHelper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(COL_ID, disease.getDiseaseNameRow());
		values.put(COL_RESOURCE_DBPEDIA_URL, disease.getResourceDBPedia());
		db.insertWithOnConflict(DISEASE_TABLE_NAME, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);

	}

	public void insertResult(Result result, String idDisease, int idConsult) {

		db = dbHelper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(COL_SCORE, result.getScore());
		values.put(COL_ID, result.getResultId());
		if (result.isUtility()) {
			values.put(COL_UTILITY, 1);
		} else {
			values.put(COL_UTILITY, 0);

		}
		values.put(COL_ID_CONSULTATION, idConsult);
		values.put(COL_ID_DISEASE, idDisease);

		db.insertWithOnConflict(RESULT_TABLE_NAME, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);

	}

	public Cursor queryResults(int idConsultation) {
		String where = COL_ID_CONSULTATION + "=?";
		String[] args = { idConsultation + "" };
		Cursor cursor = db.query(RESULT_TABLE_NAME, null, where, args, null,
				null, null);

		cursor.moveToFirst();
		return cursor;
	}
	
	public Cursor queryConsultations() {
		
		db = dbHelper.getReadableDatabase();
		
		Cursor cursor = db.query(CONSULTATION_TABLE_NAME, null, null, null,
				null, null, null);

		cursor.moveToFirst();
		return cursor;
	}
	
	public RowDiseaseAbstraction queryRowDisease(String id) {
		String where = COL_ID + "=?";
		String[] args = { id + "" };
		Cursor cursor = db.query(DISEASE_TABLE_NAME, null, where, args, null,
				null, null);

		cursor.moveToFirst();

		RowDiseaseAbstraction disease = new RowDiseaseAbstraction();

		disease.setDiseaseNameRow(cursor.getString(cursor
				.getColumnIndex(COL_ID)));
		disease.setResourceDBPedia(cursor.getString(cursor
				.getColumnIndex(COL_RESOURCE_DBPEDIA_URL)));

		return disease;
	}
	
	/**
	 * Creates a new user with the given email and password.
	 * @param email The email of the new user.
	 * @param password The password of the new user.
	 * @param token 
	 */
	public void createUser(String email, String password, int id, String token)
	{
		ContentValues values = new ContentValues();
		values.put(COL_USER_NAME, email);
		values.put(COL_PASSWORD, password);
		values.put(COL_ID, id);
		values.put(COL_TOKEN, token);
		try {
			db = dbHelper.getWritableDatabase();
			db.insert(USER_TABLE_NAME, null, values);
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
	}
	
	/**
	 * Validates if a user exists in the database. 
	 * @return <b><i>True</i></b> if a user exists, <b><i>False</i></b> on the contrary.
	 */
	public boolean hasUser()
	{
		boolean has = false;
		try {
			db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query(USER_TABLE_NAME, new String[]{COL_ID}, null, null, null, null, null);
			has = cursor.moveToFirst();
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return has;
	}
	
	/**
	 * Returns the user data in an array of strings.
	 * @return data An array containing two strings, the <b>first</b> one is the user's <b>email</b> and the <b>second</b> one is the user's <b>password</b>.
	 */
	public HashMap<String, String> getUserData()
	{
		HashMap<String, String> data = new HashMap<String, String>();
		try {
			db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query(USER_TABLE_NAME, new String[]{COL_USER_NAME, COL_PASSWORD}, null, null, null, null, null);
			cursor.moveToFirst();
			data.put(EMAIL, cursor.getString(0));
			data.put(PASSWORD, cursor.getString(1));
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return data;
	}
	
	private int getMaxId(String table) {
		int id = -1;

		db = dbHelper.getReadableDatabase();
		try {
			Cursor cursor = db.query(table, null, null, null, null, null,
					COL_ID + " DESC", 1 + "");
			cursor.moveToFirst();
			id = cursor.getInt(cursor.getColumnIndex(COL_ID));
		} catch (Exception e) {
			Log.d(TAG, "No hay registros en la tabla especificada");
		}
		return id;
	}
	
	public void saveAttempt(Attempt attempt)
	{
		try {
			db = dbHelper.getWritableDatabase();
			AttemptModel attemptModel = new AttemptModel();
			attemptModel.setSeconds(attempt.getDuration());
			attemptModel.setStudent_id(attempt.getStudent_id());
			attemptModel.setTest_id(attempt.getMytest_id());
			ArrayList<Answer> answers = attempt.getAnswers();
			attemptDao = new AttemptDao(new AttemptTableDefinition(), db);
			attemptDao.save(attemptModel);
			
			db = dbHelper.getReadableDatabase();
			attemptDao = new AttemptDao(new AttemptTableDefinition(), db);
			
			long attempt_id = getAttemptModelIdFromAttempt(attempt);
			
			AnswerModel a;
			db = dbHelper.getWritableDatabase();
			answerDao = new AnswerDao(new AnswerTableDefiniton(), db);
			for(Answer answer : answers)
			{
				a = new AnswerModel();
				a.setOption_id(answer.getOption_id());
				a.setQuestion_id(answer.getQuestion_id());
				a.setAttempt_id(attempt_id);
				answerDao.save(a);
			}
			db.close();
			
		} catch (Exception e) {
			Log.d(TAG, e.getMessage());
			e.printStackTrace();
		}
	}
	
	public long getAttemptModelIdFromAttempt(Attempt attempt) throws Exception
	{
		long attempt_id = -1;
		db = dbHelper.getReadableDatabase();
		attemptDao = new AttemptDao(new AttemptTableDefinition(), db);
		
		attempt_id = attemptDao.getByClause("SECONDS = ? AND STUDENT_ID = ? AND TEST_ID = ?",
				new String[]{String.valueOf(attempt.getDuration()), 
				String.valueOf(attempt.getStudent_id()),
				String.valueOf(attempt.getMytest_id())}).getId();
		return attempt_id;
	}
	
	public void saveAnswerToAttempt(Attempt attempt, Answer answer)
	{
		try {
			long attempt_id = getAttemptModelIdFromAttempt(attempt);
			AnswerModel model = new AnswerModel();
			model.setAttempt_id(attempt_id);
			model.setOption_id(answer.getOption_id());
			model.setQuestion_id(answer.getQuestion_id());
			
			db = dbHelper.getWritableDatabase();
			answerDao = new AnswerDao(new AnswerTableDefiniton(), db);
			answerDao.save(model);
			
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
	}
	
	public ArrayList<TestModel> getStartedTests()
	{
		ArrayList<TestModel> models = new ArrayList<TestModel>();
		
		try {
			db = dbHelper.getReadableDatabase();
			testDao = new TestDao(new TestTableDefintion(), db);
			ArrayList<TestModel> unansweredTests = (ArrayList<TestModel>) testDao.
					getAllbyClause("ANSWERED = ?", new String[]{String.valueOf(0)}, 
							null, null, null);
			for(TestModel model : unansweredTests)
			{
				if(testWasStarted(model, db));
				{
					models.add(model);
				}
			}
			db.close();
			
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return models;
	}
	
	public boolean testWasStarted(TestModel model, SQLiteDatabase db) throws Exception
	{
		boolean started = false;
		attemptDao = new AttemptDao(new AttemptTableDefinition(), db);
		AttemptModel a = attemptDao.getByClause("TEST_ID = ?", 
				new String[]{String.valueOf(model.getId())});
		started = a!= null;		
		return started;
	}

	public void saveTest(TestLogic test)
	{
		Date now;  
		try {
			
			db = dbHelper.getWritableDatabase();
			TestModel testModel = new TestModel();
			testModel.setId(test.getId());
			testModel.setInitial_range(test.getInitial_range());
			testModel.setFinal_range(test.getFinal_range());
			DateFormat df = new SimpleDateFormat(DermatologiaUniandesApp.DATE_PATTERN, Locale.US);
			String final_date = df.format(test.getEnd_date());
			String start_date = df.format(test.getStart_date());
			testModel.setFinal_date(final_date);
			testModel.setInitial_date(start_date);
			testModel.setName(test.getName());
			testModel.setDescription(test.getDescription());
			testModel.setSeconds(test.getSeconds());
			
			testDao = new TestDao(new TestTableDefintion(), db);
			if(testDao.get(testModel.getId()) != null)
			{
				testDao.update(testModel, testModel.getId());
			}
			else
			{
				testDao.save(testModel);
			}
			
			db.close();
			
			Anamnesis anamnesis;
			PhysicalExamination examination;
			CaseModel caseModel = new CaseModel();
			AnamnesisModel anamnesisModel = new AnamnesisModel();
			ExaminationModel examinationModel = new ExaminationModel();
			QuestionModel questionModel = new QuestionModel();
			OptionModel optionModel = new OptionModel();
			long modelId;
			
			
			long case_id = 0, question_id = 0;
			String gender, phototype, birthDate;
			//Save cases
			
			ArrayList<CaseLogic> cases = test.getCaseLogics();
			
			if(cases!= null)
			{
				for(CaseLogic c: test.getCaseLogics())
				{
					db = dbHelper.getWritableDatabase();
					caseModel.setId(c.getId());
					caseModel.setDescription(c.getDescription());
					caseModel.setTest_id(testModel.getId());
					caseDao = new CaseDao(new CaseTableDefintion(), db);
					if(caseDao.get(caseModel.getId())!=null)
					{
						caseDao.update(caseModel, caseModel.getId());
					}
					else
					{
						caseDao.save(caseModel);
					}
					
					db = dbHelper.getReadableDatabase();
					//db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy)
					  
					
					case_id = caseModel.getId();
					
					db = dbHelper.getWritableDatabase();
					
					anamnesis = c.getAnamnesis();
					gender = anamnesis.getGenderString();
					phototype = anamnesis.getPhototypeString();
					birthDate = anamnesis.getBirthDateString();
					
					anamnesisModel.setGender(gender);
					anamnesisModel.setAge("");
					anamnesisModel.setBirthdate(birthDate);
					anamnesisModel.setPhototype(phototype);
					anamnesisModel.setCase_id(case_id);
					now = new Date();
					anamnesisModel.setDate_added(now);
					anamnesisDao = new AnamnesisDao(new AnamnesisTableDefintion(), db);
					
					modelId = anamnesisDao.getModelId(anamnesisModel); 
					if(modelId != -1)
					{
						anamnesisDao.update(anamnesisModel, modelId);
					}
					else
					{
						anamnesisDao.save(anamnesisModel);
					}
					
					
					examination = c.getExamination();
					examinationModel.setBodyParts(examination.getBodyParts());
					examinationModel.setBorder(examination.getBorderString());
					examinationModel.setDistribution(examination.getDistributionString());
					examinationModel.setLesionType(examination.getLesionTypeString());
					examinationModel.setNumber(examination.getNumberString());
					examinationModel.setPast(examination.getPast());
					examinationModel.setShape(examination.getShapeString());
					examinationModel.setSymptoms(examination.getSymptoms());
					examinationModel.setCase_id(case_id);
					now = new Date();
					examinationModel.setDate_added(now);
					
					examinationDao = new ExaminationDao(new ExaminationTableDefintion(), db);
					
					modelId = examinationDao.getModelId(examinationModel);
					if(modelId != -1){
						examinationDao.update(examinationModel, modelId);
					}
					else
					{
						examinationDao.save(examinationModel);
					}				
					
					for(Question q: c.getQuestions())
					{
						questionModel.setCase_id(case_id);
						question_id = q.getId();
						questionModel.setId(question_id);
						questionModel.setContent(q.getContent());
						questionModel.setType(q.getType());
						
						questionDao = new QuestionDao(new QuestionTableDefintion(), db);
						if(questionDao.get(question_id)!= null)
						{
							questionDao.update(questionModel, question_id);
						}
						else
						{
							questionDao.save(questionModel);
						}
						
						for(Option o: q.getOptions())
						{
							optionModel.setContent(o.getContent());
							optionModel.setId(o.getId());
							optionModel.setQuestion_id(question_id);
							optionDao = new OptionDao(new OptionTableDefintion(), db);
							if(optionDao.get(optionModel.getId())!= null)
							{
								optionDao.update(optionModel, optionModel.getId());
							}
							else
							{
								optionDao.save(optionModel);
							}
						}					
					}
					
				}
			}
			
			ArrayList<Question> questions = test.getQuestions();
			if(questions != null)
			{
				//Save Questions
				for(Question q: test.getQuestions())
				{
					questionModel.setCase_id(case_id);
					question_id = q.getId();
					questionModel.setId(question_id);
					questionModel.setContent(q.getContent());
					questionModel.setType(q.getType());
					
					questionDao = new QuestionDao(new QuestionTableDefintion(), db);
					if(questionDao.get(question_id)!= null)
					{
						questionDao.update(questionModel, question_id);
					}
					else
					{
						questionDao.save(questionModel);
					}
					
					for(Option o: q.getOptions())
					{
						optionModel.setContent(o.getContent());
						optionModel.setId(o.getId());
						optionModel.setQuestion_id(question_id);
						optionDao = new OptionDao(new OptionTableDefintion(), db);
						if(optionDao.get(optionModel.getId())!= null)
						{
							optionDao.update(optionModel, optionModel.getId());
						}
						else
						{
							optionDao.save(optionModel);
						}
					}					
				}
			}
			db.close();
			
		} catch (Exception e) {
			Log.e(TAG, "Error: " + e.getMessage());
			e.printStackTrace();
		}
	}
	
	public void setAnswered(long testId)
	{
		try {
			db = dbHelper.getReadableDatabase();
			testDao = new TestDao(new TestTableDefintion(), db);
			TestModel model = testDao.get(testId);
			model.setAnswered(1);
			db = dbHelper.getWritableDatabase();
			testDao = new TestDao(new TestTableDefintion(), db);
			testDao.update(model, testId);
			db.close();
		} catch (Exception e) {
			Log.e(TAG, "Error: "+e.getMessage());
		}
	}
	
	
	public int getUserId() {
		int id = -1;
		try {
			db = dbHelper.getReadableDatabase();
			Cursor c = db.query(USER_TABLE_NAME, new String[]{COL_ID}, null, null,
					null, null, null, "1");
			c.moveToFirst();
			id = c.getInt(0);
			
		} catch (Exception e) {
			Log.e(TAG, "Error: "+ e.getMessage());
		}
		return id;
	}
	
	public String getUserToken()
	{
		String token = "";
		try {
			db = dbHelper.getReadableDatabase();
			Cursor c = db.query(USER_TABLE_NAME, new String[]{COL_TOKEN}, null, null,
					null, null, null, "1");
			c.moveToFirst();
			token = c.getString(0);
			
		} catch (Exception e) {
			Log.e(TAG, "Error: "+ e.getMessage());
		}
		return token;
	}
	
	public ArrayList<TestModel> getAllTests() {
		ArrayList<TestModel> models = new ArrayList<TestModel>();
		try {
			db = dbHelper.getReadableDatabase();
			testDao = new TestDao(new TestTableDefintion(), db);
			models = (ArrayList<TestModel>) testDao.getAll();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return models;
	}
	
	public ArrayList<CaseModel> getTestCases(int id)
	{
		ArrayList<CaseModel> cases = new ArrayList<CaseModel>();
		try {
			db = dbHelper.getReadableDatabase();
			caseDao = new CaseDao(new CaseTableDefintion(), db);
			cases = (ArrayList<CaseModel>) caseDao.getAllbyClause(" TEST_ID = ? ", new String[]{String.valueOf(id)},
					null, null, null);
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return cases;
	}
	
	public ArrayList<QuestionModel> getTestQuestions(int id)
	{
		ArrayList<QuestionModel> questions = new ArrayList<QuestionModel>();
		try {
			db = dbHelper.getReadableDatabase();
			questionDao = new QuestionDao(new QuestionTableDefintion(), db);
			questions = (ArrayList<QuestionModel>) questionDao.getAllbyClause(" TEST_ID = ? ", new String[]{String.valueOf(id)},
					null, null, null);
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return questions;
	}
	
	public ArrayList<QuestionModel> getCaseQuestions(int id)
	{
		ArrayList<QuestionModel> questions = new ArrayList<QuestionModel>();
		try {
			db = dbHelper.getReadableDatabase();
			questionDao = new QuestionDao(new QuestionTableDefintion(), db);
			questions = (ArrayList<QuestionModel>) questionDao.getAllbyClause(" CASE_ID = ? ", new String[]{String.valueOf(id)},
					null, null, null);
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return questions;
	}
	
	public AnamnesisModel getAnamnesisById(int id)
	{
		AnamnesisModel model = new AnamnesisModel();
		try {
			db = dbHelper.getReadableDatabase();
			anamnesisDao = new AnamnesisDao(new AnamnesisTableDefintion(), db);
			model = anamnesisDao.getByClause("CASE_ID = ?",	new String[]{String.valueOf(id)});
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return model;
	}
	
	public ExaminationModel getExaminationById(int id)
	{
		ExaminationModel model = new ExaminationModel();
		try {
			db = dbHelper.getReadableDatabase();
			examinationDao = new ExaminationDao(new ExaminationTableDefintion(), db);
			model = examinationDao.getByClause("CASE_ID = ?",	new String[]{String.valueOf(id)});
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return model;
	}
	
	public ArrayList<OptionModel> getQuestionOptions(int id)
	{
		ArrayList<OptionModel> options = new ArrayList<OptionModel>();
		try {
			db = dbHelper.getReadableDatabase();
			optionDao = new OptionDao(new OptionTableDefintion(), db);
			options = (ArrayList<OptionModel>) optionDao.getAllbyClause(" QUESTION_ID = ? ", new String[]{String.valueOf(id)},
					null, null, null);
			db.close();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
			e.printStackTrace();
		}
		return options;
	}
	
	
	/**
	 * Helper that creates the database and the tables associated with the application.
	 */
	class DBHelper extends SQLiteOpenHelper {

		public DBHelper() {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@SuppressWarnings("static-access")
		@Override
		public void onCreate(SQLiteDatabase db) {

			// Consultation table creation
			String sql = String
					.format("create table %s (%s integer primary key autoincrement, %s int)",
							CONSULTATION_TABLE_NAME, COL_ID, COL_CREATED_AT);
			db.execSQL(sql);

			// Disease table creation
			sql = String.format(
					"create table %s (%s text primary key, %s text)",
					DISEASE_TABLE_NAME, COL_ID, COL_RESOURCE_DBPEDIA_URL);
			db.execSQL(sql);

			// Result table creation
			sql = String
					.format("create table %s (%s integer primary key, %s int , %s int, %s int not null, %s text not null, foreign key (%s) REFERENCES %s (%s),foreign key (%s) REFERENCES %s (%s))",
							RESULT_TABLE_NAME, COL_ID, COL_SCORE, COL_UTILITY,
							COL_ID_CONSULTATION, COL_ID_DISEASE,
							COL_ID_CONSULTATION, CONSULTATION_TABLE_NAME,
							COL_ID, COL_ID_DISEASE, DISEASE_TABLE_NAME, COL_ID);
			db.execSQL(sql);
			
			sql = String
					.format("create table %s (%s integer primary key, %s text unique, %s text, %s text unique)",
							USER_TABLE_NAME, COL_ID, COL_USER_NAME, COL_PASSWORD, COL_TOKEN);
			db.execSQL(sql);
			
			try {
				
				TestTableDefintion ttd = new TestTableDefintion();
				ttd.onCreate(db);
				
				CaseTableDefintion ctd = new CaseTableDefintion();
				ctd.onCreate(db);
				
				AnamnesisTableDefintion atd = new AnamnesisTableDefintion();
				atd.onCreate(db);
				
				ExaminationTableDefintion etd = new ExaminationTableDefintion();
				etd.onCreate(db);
				
				QuestionTableDefintion qtd = new QuestionTableDefintion();
				qtd.onCreate(db);
				
				OptionTableDefintion otd = new OptionTableDefintion();
				otd.onCreate(db);
				
				AttemptTableDefinition attd = new AttemptTableDefinition();
				attd.onCreate(db);
				
				AnswerTableDefiniton anstd = new AnswerTableDefiniton();
				anstd.onCreate(db);
				
			} catch (Exception e) {
				Log.e(TAG, "Error: "+e.getMessage());
			}
			
			
			
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			onCreate(db);
		}
	}


	




	
}
